/*
 * SQLGenerator.java
 *
 * $Id: SQLGenerator.java,v 1.3 2009-03-11 10:49:08 juancarlos Exp $
 *
 * Written by: Mario García García <mario@imagos.es>
 * (c) 2007 Fundación Centro Tecnológico de la Carne
 */
package org.ceteca.explica.server.util.db;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.Stack;

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Restrictions;


/**
 * Business Logic class that implements an engine for
 * generating SQL statements. 
 * <br/>
 * Responsibilities:
 * <ul>
 * 	<li>SQL statements generation.</li>
 * </ul>
 * <br/>
 * @author Mario García García <mario@imagos.es>
 * Copyright (c) 2007 Fundación Centro Tecnolóxico da Carne
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * <br/>
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * <br/>
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
public class SQLGenerator {
    public SQLGenerator() {
    }
    
    /**
     * Build and return the SQL query made from the sql query arguments.
     * @param tableName String, Name of the queried table (or view).
     * @param lFields ArrayList, List with all the fields the result is made of.
     * @param lWhereCriteria ArrayList, List with all the query criteria that will
     * made the WHERE statement of the SQL query.
     * @param lOrderCriteria ArrayList, List with all the order by criteria that will
     * made the ORDER BY statement of the SQL query.
     * @return String, SQL query made from the arguments.
     */
    public String getSQLQuery(String tableName, ArrayList lFields, ArrayList lWhereCriteria, 
    		ArrayList lOrderCriteria) {
            
        // Store to keep the generated SQL query.
        StringBuffer sql = new StringBuffer();
        
        // At least one field must be defined for the select clause of the SQL query.
        if (lFields != null && lFields.size() >0) {
        	// Add the SELECT clause
            sql.append("SELECT ");
            // Iterate through all the SELECT fields.
            Iterator it = lFields.iterator();
            while (it != null && it.hasNext()) {
                // Add the field name to the SELECT clause of the SQL query.
                String key = (String)it.next();
                sql.append(key);
                // If there are more fields in the SELECT clause of the SQL query then
                // add a field delimiter (,).
                if (it.hasNext())
                	sql.append(", ");
            } // while exist any other one selected field.

            // Add the FROM clause
            sql.append(" FROM ");
            sql.append(tableName);
            sql.append(" ");
            
            // Set on the flag to disable the criteria delimiter insertion for the first criteria 
            // in the WHERE clause.
            boolean firstCriteria = true;
            // If at least one search criteria has been defined then add the WHERE clause.
            if (lWhereCriteria != null && lWhereCriteria.size() > 0) {
                sql.append("WHERE ");
            } // if lWhereCriteria not null
            
            // Build the rest of the WHERE clause by appending all the search criteria defined.
            for (int i=0; lWhereCriteria != null && i<lWhereCriteria.size(); i++) {
                CriteriaBean crit = (CriteriaBean)lWhereCriteria.get(i);
                if (crit != null) {
                	// Append the search criteria to the WHERE clause of the SQL query.
                    sql.append(crit.getCriteriaAsSql(firstCriteria));
                } // if crit not null
                // Set off the flag, to enable the criteria delimiter insertion for the rest of 
                // the criteria in the WHERE clause 
                firstCriteria = false;
            } // for each of the search criteria
        
            // If any order criteria has been defined then add the ORDER BY clause. 
            if (lOrderCriteria != null && lOrderCriteria.size() > 0) {
                sql.append(" ORDER BY ");
            } // if lOrderCriteria not null
            
            // Set on the flag to disable the criteria delimiter insertion for the first criteria 
            // in the ORDER BY clause.
            firstCriteria = true;
            
            // Build the rest of the ORDER BY clause by appending all the order criteria defined.
            for (int i=0; lOrderCriteria != null && i<lOrderCriteria.size(); i++) {
                CriteriaBean crit = (CriteriaBean)lOrderCriteria.get(i);
                if (crit != null) {
                	// Append the order criteria to the ORDER BY clause of the SQL query.
                    sql.append(crit.getOrderCriteriaAsSql(firstCriteria));
                } // if crit not null
                // Set off the flag, to enable the criteria delimiter insertion for the rest of 
                // the criteria in the ORDER BY clause 
                firstCriteria = false;
            } // for each of the order criteria
        } // if there are any selected fields.
        else
            sql = null;
        
        System.out.println(sql);
        
        return (sql == null)?null:sql.toString();
    }
    
    /**
     * Build and return the where clause of the sql query made from the sql 
     * searching criteria.
     * @param session Session, Hibernate session used to create the criteria instance.
     * @param tableName String, Name of the queried table (or view).
     * @param lWhereCriteria ArrayList, List with all the query criteria that will
     * made the WHERE statement of the SQL query.
     * @return Criteria, where clause of the SQL query built from the searching
     * criteria.
     */
    public Criteria getSQLCriteria(Session session, String tableName, ArrayList lWhereCriteria) {
    	try {
    		Class entityClass = Class.forName(tableName);
    		
    		return this.getSQLCriteria(session, entityClass, lWhereCriteria);
    	}
    	catch (Exception ex) {
    		ex.printStackTrace();
    		return null;
    	}
    }
    
    /**
     * Build and return the where clause of the sql query made from the sql 
     * searching criteria.
     * @param session Session, Hibernate session used to create the criteria instance.
     * @param entity Class, Entity (Table or View) that will be searched.
     * @param lWhereCriteria ArrayList, List with all the query criteria that will
     * made the WHERE statement of the SQL query.
     * @return Criteria, where clause of the SQL query built from the searching
     * criteria.
     */
    public Criteria getSQLCriteria(Session session, Class entity, ArrayList lWhereCriteria) {
    	// Instance of the stack that will hold all the criteria levels.
    	// The criteria may use brackets to specify a group of criteria. Each
    	// bracket inits a new level. Each of the levels will have a queue of
    	// built criteria.
    	Stack<CriteriaQueue> critLevelStack = new Stack<CriteriaQueue>();
    	
    	// Instance of the queue that will hold all the criteria of one level.
    	// For building the criteria a queue will be used to store the criterion
    	// appended with or operations. The and operators have a maximum priority
    	// so they will be built immediately. On the contrary, the or operators have
    	// less priority so they must be queued until an and appears or the level
    	// is finished.
    	CriteriaQueue critQueue = new CriteriaQueue();
    	
    	// Flag to show when a new level of criteria is started. The first criterion
    	// of each level won't have any logic operator.
    	boolean firstCriterion = true;
    	
    	// Build each one of the criteria that will form the WHERE clause of the query
    	for (int i=0; lWhereCriteria != null && i<lWhereCriteria.size(); i++) {
			// Get the criteria from the list
            CriteriaBean crit = (CriteriaBean)lWhereCriteria.get(i);
            if (crit != null) {
            	// Get the brackets prefix
            	String openBrackets = crit.getFieldBracketsPrefix();
            	
            	// If the criteria inits a new group (has any bracket)
            	if (openBrackets != null && openBrackets.length() > 0) {
            		// for each open bracket
            		for (int b = 0; b < openBrackets.length(); b++) {
                		// Add the criteria queue to the top level of the stack.
                		critLevelStack.add(critQueue);
                		
                		// Start a new Criteria queue for the new level
                		critQueue = new CriteriaQueue(crit.getFieldLogicOperator());
                		
                		// Set the flag of starting new level
                		firstCriterion = true;
            		} // for each open bracket
            	} // if has any open bracket
            	
            	// Get the logic operator used to link the criterion
            	// to the where criteria list.
            	String logicOp = crit.getFieldLogicOperator();
            	
            	// If the criterion has no logic operator or it's the first criterion
            	if (logicOp == null || logicOp.length() <= 0 || firstCriterion) {
            		// Add the criterion to the queue
            		critQueue.getQueue().add(crit.getCriteria());
            	} // else no operator
            	// If the criterion has an OR logic operator
            	else if (logicOp.equals(InterfaceDB.LOGIC_OPERATOR_OR)) {
            		// Add the criterion to the queue
            		critQueue.getQueue().add(crit.getCriteria());
            	} // else OR operator
            	// If the criterion has an AND logic operator
            	else if (logicOp.equals(InterfaceDB.LOGIC_OPERATOR_AND)) {
            		// Get the last criterion from the queue and build
            		// an AND criterion
            		Criterion prevCrit = critQueue.getQueue().removeLast();
            		Criterion andCrit = null;
            		if (prevCrit != null) {
            			andCrit = Restrictions.and(
            				prevCrit,
            				crit.getCriteria()
            			);
            		}
            		else {
            			andCrit = crit.getCriteria();
            		}
            		
            		// Add the built and criterion to the queue
            		critQueue.getQueue().add(andCrit);
            	} // else AND operator
            	
            	String closeBrackets = crit.getFieldBracketsSuffix();
            	
            	// If the criteria finishes a group (has any close bracket)
            	if (closeBrackets != null && closeBrackets.length() > 0) {
            		// for each close bracket
            		for (int b = 0; b < closeBrackets.length(); b++) {
            			// Append the criteria that may be stored in this level's queue
            			Criterion orCrit = this.buildLevelCriterion(critQueue.getQueue());
            			
            			// Get the logic operator used to link the grouped criteria
            			String levelLogicOp = critQueue.getLogicOp();
            			
                		// Get the previous level criterion queue from the stack
            			critQueue = critLevelStack.pop();

            			// If the inner level was linked to the topper level with an AND operator
            			if (levelLogicOp != null && levelLogicOp.equals(InterfaceDB.LOGIC_OPERATOR_AND)) {
            				// Get the last criterion from the queue and build the and criterion
            				Criterion prevCrit = critQueue.getQueue().removeLast();
            				Criterion andCrit = null;
            				if (prevCrit != null) {
            					andCrit = Restrictions.and(
                    				prevCrit,
                    				orCrit
            					);
            				}
            				else
            					andCrit = orCrit;
                    		
                    		// Add the built and criterion to the queue
                    		critQueue.getQueue().add(andCrit);
            			} // if (levelLogicOp == AND)
            			// else the inner level was linked with an OR operator
            			else {
            				// Add the OR criterion to the queue of the previous level
            				critQueue.getQueue().add(orCrit);
            			} // else (levelLogicOp != AND)
            		} // for each close bracket
            	} // if has any close bracket
            	
            } // if crit not null
            // Set off the flag that shows when the first criterion of the level is being built 
            firstCriterion = false;
        } // for each of the search criteria

    	// Build the toppest level of criteria
    	Criterion resultCrit = null;
    	if (critQueue != null && critQueue.getQueue() != null && critQueue.getQueue().size() > 0) {
    		resultCrit = this.buildLevelCriterion(critQueue.getQueue());
    	}
    	
    	// Criteria instance to store the built criteria.
    	Criteria result = session.createCriteria(entity);
    	if (resultCrit != null)
    		result.add(resultCrit);

    	return result;
    }
    
	/**
	 * Builds the OR Criterion for appending all the criteria stored
	 * in the criteria queue for the level.
	 * @return Criterion, OR criterion built for this criteria level's queue.
	 */
	private Criterion buildLevelCriterion(LinkedList<Criterion> critQueue) {
		if (critQueue == null)
			return null;
		// If the queue has only one element
		else if (critQueue.size() == 1) {
			// return the last element of the queue without logic operator
			return critQueue.poll();
		} // if (queue has only one element)
		// If the queue has more than one element
		else {
			// Get the top criterion of the queue
			Criterion firstCrit = critQueue.poll();
			
			// Build the second criterion with the rest of the criteria in the queue
			Criterion secondCrit = this.buildLevelCriterion(critQueue);
			
			// Build an OR criterion with the first and second criterion
			Criterion orCrit = Restrictions.or(
    				firstCrit,
    				secondCrit
    		);
			
			// Return the built OR criterion
			return orCrit;
		} // else (queue has many elements)
	}


    /**
	 * Returns tag Id assigned to CVS source file.
	 */
	public static String getRevision() {
		return "$Id: SQLGenerator.java,v 1.3 2009-03-11 10:49:08 juancarlos Exp $";
	}
}
